﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq.Mapping;
using System.Collections.ObjectModel;
using System.Data.Linq;
using MLY.Dal;

namespace MLY.Bll
{
    public enum woStatus { updated, inserted, failed };

    public enum BalanceType
    {
        ByRequestedDate, ByPlannedDate
    }


    public class clsWorkOrder
    {

        public static DateTime CONST_DateDeleted = new DateTime(2099, 1, 1);
        public static DateTime CONST_SomeFutureDate = new DateTime(2090, 12, 31);
        private const bool ToCheckDates = false;

        //public enum BalanceType
        //{
        //  ByRequestedDate, ByPlannedDate
        //}

        public clsWorkOrder()
        {
            _DataRow = new TableWorkOrder();
        }

        // Select the work order that its WorkOrderId equal to woId.
        public clsWorkOrder(int woId)
        {
            _DataRow = dc.TableWorkOrders.Where(S => S.WorkOrderId == woId).FirstOrDefault();
            if (_DataRow != null)
                IsExist = true;
        }

        public clsWorkOrder(int woId, bool latestDist)
        {
            _DataRow = dc.TableWorkOrders.Where(S => S.WorkOrderId == woId).FirstOrDefault();
            if (_DataRow != null)
                IsExist = true;
        }

        public clsWorkOrder(TableWorkOrder val)
        {
            _DataRow = new MLY.Dal.TableWorkOrder();
            _DataRow.TotalSum = val.TotalSum;
            _DataRow.TextFieldB = val.TextFieldB;
            _DataRow.TextFieldC = val.TextFieldC;
            _DataRow.TextFieldA = val.TextFieldA;
            _DataRow.DateFieldA = val.DateFieldA;
            _DataRow.DateFieldB = val.DateFieldB;
            _DataRow.SplitId = val.SplitId;
            _DataRow.SendDate = val.SendDate;
            _DataRow.RequestedDelivaryDate = val.RequestedDelivaryDate;
            _DataRow.Remarks = val.Remarks;
            _DataRow.Quantity = val.Quantity;
            _DataRow.ProductDescription = val.ProductDescription;
            _DataRow.PlannedDelivaryDate = val.PlannedDelivaryDate;
            _DataRow.OrderNumber = val.OrderNumber;
            _DataRow.CustomerWorkOrderNumber = val.CustomerWorkOrderNumber;
            _DataRow.CustomerPriority = val.CustomerPriority;
            _DataRow.CustomerId = val.CustomerId;
            _DataRow.CustomerCatalougeCode = val.CustomerCatalougeCode;
            _DataRow.ContractorWorkOrderNumber = val.ContractorWorkOrderNumber;
            _DataRow.ContractorLotNumber = val.ContractorLotNumber;
            _DataRow.ContractorId = val.ContractorId;
            _DataRow.ContractorCatalogueCode = val.ContractorCatalogueCode;
            _DataRow.ActualDelivaryDate = val.ActualDelivaryDate;
            _DataRow.CustomerOrderNumber = val.CustomerOrderNumber;
            _DataRow.Project = val.Project;
            _DataRow.originalActualDeliveryDate = val.originalActualDeliveryDate;
            _DataRow.disqualifyDate = val.disqualifyDate;
            _DataRow.originalWOForDisqualify = val.originalWOForDisqualify;
        }

        public static void SplitWorkOrder(int workOrderID, decimal splitQuantity)
        {
            // Select the work order that its WorkOrderId equal to woId.
            clsWorkOrder workOrder = new clsWorkOrder(workOrderID);
            workOrder.SplitWorkOrder(splitQuantity, false);
        }
        public static int SplitWorkOrderWithDisqalify(int workOrderID, decimal splitQuantity)
        {
            // Select the work order that its WorkOrderId equal to woId.
            clsWorkOrder workOrder = new clsWorkOrder(workOrderID);
            int NewWOId = workOrder.SplitWorkOrder(splitQuantity, true);
            return NewWOId;
        }

        // Reduce current quantity by splitQuantity and create a new work worder
        // with quanttity == splitQuantity.
        private int SplitWorkOrder(decimal splitQuantity, bool disqualify)
        {
            if (splitQuantity > Data.Quantity)
                throw new Exception("Split amount is larger then quantity, disqualify is: " + disqualify.ToString());
            if (splitQuantity == 0)
                throw new Exception("Zero split quantity, disqualify is: " + disqualify.ToString());


            var query = from p in dc.TableWorkOrders select p;
            query = query.Where(S => S.CustomerId == Data.CustomerId);
            query = query.Where(S => S.ContractorId == Data.ContractorId);
            if (Data.CustomerWorkOrderNumber == null)
                query = query.Where(S => S.CustomerWorkOrderNumber == null);
            else
                query = query.Where(S => S.CustomerWorkOrderNumber == Data.CustomerWorkOrderNumber);
            if (Data.ContractorWorkOrderNumber == null)
                query = query.Where(S => S.ContractorWorkOrderNumber == null);
            else
                query = query.Where(S => S.ContractorWorkOrderNumber == Data.ContractorWorkOrderNumber);
            if (Data.ContractorLotNumber == null)
                query = query.Where(S => S.ContractorLotNumber == null);
            else
                query = query.Where(S => S.ContractorLotNumber == Data.ContractorLotNumber);
            if (Data.CustomerPriority == null)
                query = query.Where(S => S.CustomerPriority == null);
            else
                query = query.Where(S => S.CustomerPriority == Data.CustomerPriority);
            if (Data.CustomerCatalougeCode == null)
                query = query.Where(S => S.CustomerCatalougeCode == null);
            else
                query = query.Where(S => S.CustomerCatalougeCode == Data.CustomerCatalougeCode);
            //if (Data.ProductDescription == null)
            //    query = query.Where(S => S.ProductDescription == null);
            //else
            //    query = query.Where(S => S.ProductDescription == Data.ProductDescription);
            if (Data.TextFieldA == null)
                query = query.Where(S => S.TextFieldA == null);
            else
                query = query.Where(S => S.TextFieldA == Data.TextFieldA);
            if (Data.TextFieldB == null)
                query = query.Where(S => S.TextFieldB == null);
            else
                query = query.Where(S => S.TextFieldB == Data.TextFieldB);
            if (Data.TextFieldC == null)
                query = query.Where(S => S.TextFieldC == null);
            else
                query = query.Where(S => S.TextFieldC == Data.TextFieldC);
            //if (Data.Remarks == null)
            //    query = query.Where(S => S.Remarks == null);
            //else
            //    query = query.Where(S => S.Remarks == Data.Remarks);
            if (Data.OrderNumber == null)
                query = query.Where(S => S.OrderNumber == null);
            else
                query = query.Where(S => S.OrderNumber == Data.OrderNumber);
            //if (Data.TotalSum == null)
            //    query = query.Where(S => S.TotalSum == null);
            //else
            //    query = query.Where(S => S.TotalSum == Data.TotalSum);
            if (Data.ContractorCatalogueCode == null)
                query = query.Where(S => S.ContractorCatalogueCode == null);
            else
                query = query.Where(S => S.ContractorCatalogueCode == Data.ContractorCatalogueCode);
            if (Data.CustomerOrderNumber == null)
                query = query.Where(S => S.CustomerOrderNumber == null);
            else
                query = query.Where(S => S.CustomerOrderNumber == Data.CustomerOrderNumber);
            query = query.OrderByDescending(S => S.SplitId);
            
            List<TableWorkOrder> relevantOrders = query.ToList<TableWorkOrder>();
            if (relevantOrders.Count == 0)
                throw new Exception("Order not found.");

            clsWorkOrder newOrder = new clsWorkOrder(_DataRow);
            if (relevantOrders[0].SplitId == null)
                newOrder._DataRow.SplitId = 1;
            else
                newOrder._DataRow.SplitId = relevantOrders[0].SplitId + 1;
            Data.Quantity -= splitQuantity;
            newOrder._DataRow.Quantity = splitQuantity;
            newOrder._DataRow.Project = Data.Project;
            newOrder._DataRow.debate = Data.debate;
            newOrder._DataRow.NewForAlert = false;

            if (disqualify)
            {
                // disqualify will not create distribution, just use originalWOForDisqualify
                newOrder._DataRow.originalActualDeliveryDate = newOrder.Data.ActualDelivaryDate;
                newOrder._DataRow.SplitId = Data.SplitId;
                newOrder._DataRow.disqualifyDate = DateTime.Now;
                newOrder._DataRow.ActualDelivaryDate = DateTime.Parse("2099-01-01");                 
                newOrder._DataRow.originalWOForDisqualify = Data.WorkOrderId;
                
            }
                
            newOrder.Save();
            Save();

            return newOrder.Data.WorkOrderId;
        }

        public void Split(decimal amount)
        {
            if (amount > Data.Quantity)
                throw new Exception("Split amount is larger then quantity");

            List<TableWorkOrder> relevantOrders = (from p in dc.TableWorkOrders
                                                   where p.WorkOrderId == Data.WorkOrderId
                                                   orderby p.SplitId descending
                                                   select p).ToList();

            clsWorkOrder newOrder = new clsWorkOrder(_DataRow);
            if (relevantOrders[0].SplitId == null)
                newOrder._DataRow.SplitId = 1;
            else
                newOrder._DataRow.SplitId = relevantOrders[0].SplitId + 1;
            Data.Quantity -= amount;
            newOrder.Data.Quantity = amount;
            newOrder.Data.NewForAlert = false;
            newOrder.Save();
            Save();
        }

        public void Save()
        {
            if (IsExist)
            {
                _DataRow.UpdateBy = SessParam.UserId;
                _DataRow.UpdateDate = DateTime.Now;
            }
            else
            {
                _DataRow.DateCreated = DateTime.Now;
                _DataRow.UpdateDate = DateTime.Now;
                _DataRow.UpdateBy = SessParam.UserId;
                dc.TableWorkOrders.InsertOnSubmit(_DataRow);
            }
            dc.SubmitChanges();
        }

        public static void CreateDist(int originalWoId, decimal quantity_sent,
                                      DateTime? actualDelivaryDate, DateTime? plannedDelivaryDate)
        {
            using (lqKbmDataContext dc = new lqKbmDataContext())
            {
                var originalWo = new clsWorkOrder(originalWoId);
                TableWorkOrder newDist = Bl_Dist.DuplicateWo(originalWo.Data);
                newDist.UpdateDate = DateTime.Now;
                newDist.DateCreated = DateTime.Now;
                newDist.UpdateBy = clsUser.CurrentUserId;
                newDist.Quantity = quantity_sent;

                if (Utils.IsSqlDate(actualDelivaryDate))
                    newDist.ActualDelivaryDate = actualDelivaryDate;
                else
                    newDist.ActualDelivaryDate = null;

                dc.TableWorkOrders.InsertOnSubmit(newDist);
                dc.SubmitChanges();
            }
        }

        public static decimal GetOpenQuantity(object workOrderId)
        {
            using (lqKbmDataContext dc = new lqKbmDataContext())
            {
                var toAdd = dc.TableWorkOrders.Where(S => S.WorkOrderId == (int)workOrderId).FirstOrDefault();
                if (toAdd.CustomerWorkOrderNumber == "654321")
                {
                }
                decimal quant = (toAdd.originalQuantity.HasValue ? toAdd.originalQuantity.Value : 0);
                var x = dc.TableWorkOrders.Where(S => S.originalWoForDist == toAdd.WorkOrderId &&
                                                      S.ActualDelivaryDate.HasValue &&
                                                      S.ActualDelivaryDate.Value.Year < 2099).Sum(Q => Q.Quantity);
                if (toAdd.ActualDelivaryDate.HasValue && toAdd.ActualDelivaryDate.Value != CONST_DateDeleted)
                {
                    x += toAdd.Quantity;
                }
                return x == null ? quant : quant - x.Value;
            }
        }

        public static decimal GetOpenQuantity(Dal.TableWorkOrder toAdd)
        {
            return GetOpenQuantity(toAdd.WorkOrderId);
        }

        public static int add(Dal.TableWorkOrder toAdd)
        {
            clsWorkOrder t = new clsWorkOrder(toAdd);
            t.Save();
            return t.Data.WorkOrderId;
        }

        public static void UpdateDists(int WorkOrderId)
        {
            using (lqKbmDataContext db = new lqKbmDataContext())
            {
                TableWorkOrder wo = db.TableWorkOrders.Where(O => O.WorkOrderId == WorkOrderId).First();

                // Update all existing distribuion lines to be like the WO.
                var v = from o in db.TableWorkOrders
                        where o.originalWoForDist == wo.WorkOrderId
                        select o;
                foreach (TableWorkOrder dist in v.ToList())
                {
                    if (dist.ContractorCatalogueCode != wo.ContractorCatalogueCode)
                        dist.ContractorCatalogueCode = wo.ContractorCatalogueCode;
                    if (dist.ContractorLotNumber != wo.ContractorLotNumber)
                        dist.ContractorLotNumber = wo.ContractorLotNumber;
                    if (dist.ContractorWorkOrderNumber != wo.ContractorWorkOrderNumber)
                        dist.ContractorWorkOrderNumber = wo.ContractorWorkOrderNumber;
                    if (dist.CustomerCatalougeCode != wo.CustomerCatalougeCode)
                        dist.CustomerCatalougeCode = wo.CustomerCatalougeCode;
                    if (dist.CustomerPriority != wo.CustomerPriority)
                        dist.CustomerPriority = wo.CustomerPriority;
                    if (dist.CustomerWorkOrderNumber != wo.CustomerWorkOrderNumber)
                        dist.CustomerWorkOrderNumber = wo.CustomerWorkOrderNumber;
                    if (dist.OrderNumber != wo.OrderNumber)
                        dist.OrderNumber = wo.OrderNumber;
                    if (dist.ProductDescription != wo.ProductDescription)
                        dist.ProductDescription = wo.ProductDescription;
                    if (dist.Remarks != wo.Remarks)
                        dist.Remarks = wo.Remarks;
                    if (dist.RequestedDelivaryDate != wo.RequestedDelivaryDate)
                        dist.RequestedDelivaryDate = wo.RequestedDelivaryDate;
                    if (dist.SendDate != wo.SendDate)
                        dist.SendDate = wo.SendDate;
                    if (dist.TextFieldA != wo.TextFieldA)
                        dist.TextFieldA = wo.TextFieldA;
                    if (dist.TextFieldB != wo.TextFieldB)
                        dist.TextFieldB = wo.TextFieldB;
                    if (dist.TextFieldC != wo.TextFieldC)
                        dist.TextFieldC = wo.TextFieldC;
                    if (dist.DateFieldA != wo.DateFieldA)
                        dist.DateFieldA = wo.DateFieldA;
                    if (dist.DateFieldB != wo.DateFieldB)
                        dist.DateFieldB = wo.DateFieldB;
                    if (dist.Project != wo.Project)
                        dist.Project = wo.Project;
                    if (dist.debate != wo.debate)
                        dist.debate = wo.debate;
                    if (dist.Project != wo.Project)
                        dist.Project = wo.Project;
                    if (dist.originalWOForDisqualify != wo.originalWOForDisqualify)
                        dist.originalWOForDisqualify = wo.originalWOForDisqualify;
                }
                db.SubmitChanges();
            }
        }

        private static bool IsSCDRecor(TableWorkOrder toCheck)
        {
            if ((toCheck.ContractorId.HasValue && toCheck.CustomerId.HasValue) &&
                (toCheck.CustomerId.Value == 25) && (toCheck.ContractorId == 46))
                return true;
            return false;    
        }

        public static woStatus InsertWoFromDictionaryFile(
                                        Dictionary<string, object> dict,
                                        bool isclient,
                                        List<string> keyFileds) // Key fields for current template.
        {
            lqKbmDataContext dc = new lqKbmDataContext();
            TableWorkOrder toAdd = new TableWorkOrder();
            
            string colNames = "";
            string IncorrectDates = "";
            int DaysUpdateAlowed = 7;

            foreach (string s in dict.Keys)
                colNames += s + ",";
            colNames = colNames.Substring(0, colNames.Length - 1);

            string[] cols = colNames.Split(',');
            object[] values = dict.Values.ToArray();

            // ONLY IN FILES
            string contractorCode = (string)GetVal("ContractorCode", dict, typeof(string));
            string clientCode = (string)GetVal("CustomerCode", dict, typeof(string));

            try
            {
                if (isclient)
                {
                    toAdd.CustomerId = SessParam.OrganizationId;
                    toAdd.ContractorId = (from p in dc.TableOrganizationRelations
                                          where p.ContarctorCode == contractorCode
                                          select p.ContractorId).FirstOrDefault();
                }
                else
                {
                    toAdd.ContractorId = SessParam.OrganizationId;
                    toAdd.CustomerId = (from p in dc.TableOrganizationRelations
                                        where p.CustomerCode == clientCode
                                        select p.CustomerId).FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                Log.WriteError("", ex);
                throw new Exception(string.Format(@"Customer\Contractor Code not valid - custCode:{0} , contactorCode:{1}", clientCode, contractorCode));
            }

            int organizationRelationCount = (from p in dc.TableOrganizationRelations
                                             where p.ContractorId == toAdd.ContractorId &&
                                                   p.CustomerId == toAdd.CustomerId
                                             select p).Count();
            if (organizationRelationCount == 0)
                throw new Exception(string.Format(@"Customer\Contractor Relation Does Not Exist! - custCode:{0} , contactorCode:{1}", clientCode, contractorCode));

            //string quantity_sent = ((string)GetVal("Quantity_sent", dict, typeof(string))); 07/07/2011
            const string cQuantity = "Quantity";
            if (!dict.Keys.Contains(cQuantity))
                throw new Exception("The Template missing '" + cQuantity + "' field");
            string quantity_sent = ((string)GetVal(cQuantity, dict, typeof(string)));

            toAdd.CustomerPriority = (int?)GetVal(TableWorkOrder.cnCustomerPriority, dict, typeof(int));
            toAdd.SplitId = (int?)GetVal(TableWorkOrder.cnSplitId, dict, typeof(int));
            toAdd.TextFieldA = ((string)GetVal(TableWorkOrder.cnTextFieldA, dict, typeof(string)));
            toAdd.TextFieldB = (string)GetVal(TableWorkOrder.cnTextFieldB, dict, typeof(string));
            toAdd.TextFieldC = (string)GetVal(TableWorkOrder.cnTextFieldC, dict, typeof(string));
            toAdd.DateFieldA = (DateTime?)GetVal(TableWorkOrder.cnDateFieldA, dict, typeof(string));
            toAdd.DateFieldB = (DateTime?)GetVal(TableWorkOrder.cnDateFieldB, dict, typeof(string));
            toAdd.CustomerWorkOrderNumber = (string)GetVal(TableWorkOrder.cnCustomerWorkOrderNumber, dict, typeof(string));
            toAdd.SendDate = Utils.ParseDate((string)GetVal(TableWorkOrder.cnSendDate, dict, typeof(string)));
            toAdd.RequestedDelivaryDate = Utils.ParseDate((string)GetVal(TableWorkOrder.cnRequestedDelivaryDate, dict, typeof(string)));
            toAdd.ContractorCatalogueCode = (string)GetVal(TableWorkOrder.cnContractorCatalogueCode, dict, typeof(string));
            toAdd.ContractorWorkOrderNumber = (string)GetVal(TableWorkOrder.cnContractorWorkOrderNumber, dict, typeof(string));
            toAdd.Remarks = (string)GetVal(TableWorkOrder.cnRemarks, dict, typeof(string));
            toAdd.OrderNumber = (string)GetVal(TableWorkOrder.cnOrderNumber, dict, typeof(string));
            toAdd.ActualDelivaryDate = Utils.ParseDate((string)GetVal(TableWorkOrder.cnActualDelivaryDate, dict, typeof(string)));
            toAdd.originalActualDeliveryDate = Utils.ParseDate((string)GetVal(TableWorkOrder.cnoriginalActualDeliveryDate, dict, typeof(string)));
            toAdd.disqualifyDate = Utils.ParseDate((string)GetVal(TableWorkOrder.cndisqualifyDate, dict, typeof(string)));
            toAdd.originalWOForDisqualify = (int?)GetVal(TableWorkOrder.cnoriginalWOForDisqualify, dict, typeof(int));

            //toAdd.Set_PlannedDelivaryDate(Utils.ParseDate((string)GetVal(TableWorkOrder.cnPlannedDelivaryDate, dict, typeof(string))));
            toAdd.PlannedDelivaryDate = Utils.ParseDate((string)GetVal(TableWorkOrder.cnPlannedDelivaryDate, dict, typeof(string)));

            toAdd.ContractorLotNumber = (string)GetVal(TableWorkOrder.cnContractorLotNumber, dict, typeof(string));
            toAdd.Quantity = (decimal?)GetVal(TableWorkOrder.cnQuantity, dict, typeof(decimal));
            toAdd.CustomerCatalougeCode = (string)GetVal(TableWorkOrder.cnCustomerCatalougeCode, dict, typeof(string));
            toAdd.ProductDescription = (string)GetVal(TableWorkOrder.cnProductDescription, dict, typeof(string));

            toAdd.UpdateBy = SessParam.UserId;
            toAdd.UpdateDate = DateTime.Now;

            toAdd.Project = ((string)GetVal(TableWorkOrder.cnProject, dict, typeof(string)) == null) ? "" : (string)GetVal(TableWorkOrder.cnProject, dict, typeof(string));
            

            toAdd.debate = false;

            //----------------------------------------------------

            string sqlParameters = string.Format("CustomerId: '{0}'", toAdd.CustomerId);
            sqlParameters += string.Format(", ContractorId: '{0}'", toAdd.ContractorId);
            var query = from p in dc.TableWorkOrders select p;
            query = query.Where(S => S.CustomerId == toAdd.CustomerId);
            query = query.Where(S => S.ContractorId == toAdd.ContractorId);
            query = query.Where(S => S.originalWoForDist == null);
            // not updating deleted orders.
            query = query.Where(S => (!S.ActualDelivaryDate.HasValue || S.ActualDelivaryDate.Value.Year < 2098));
            if (toAdd.SplitId == null)
                query = query.Where(S => S.SplitId.Equals(null));
            else
            {
                query = query.Where(S => S.SplitId.Equals(toAdd.SplitId));
                sqlParameters += string.Format(", SplitId: '{0}'", toAdd.SplitId);
            }

            foreach (string keyFiled in keyFileds)
            {
                if (keyFiled == TableWorkOrder.cnCustomerPriority)
                {
                    if (toAdd.CustomerPriority.HasValue)
                    {
                        query = query.Where(S => S.CustomerPriority.Equals(toAdd.CustomerPriority));
                        sqlParameters += string.Format(", CustomerPriority: '{0}'", toAdd.CustomerPriority);
                    }
                    else
                        query = query.Where(S => S.CustomerPriority.Equals(null) || S.CustomerPriority.Equals(""));
                }

                if (keyFiled == TableWorkOrder.cnTextFieldA)
                {
                    if (string.IsNullOrEmpty(toAdd.TextFieldA))
                        query = query.Where(S => S.TextFieldA.Equals(null) || S.TextFieldA.Equals(""));
                    else
                    {
                        query = query.Where(S => S.TextFieldA.Equals(toAdd.TextFieldA));
                        sqlParameters += string.Format(", TextFieldA: '{0}'", toAdd.TextFieldA);
                    }
                }

                if (keyFiled == TableWorkOrder.cnTextFieldB)
                {
                    if (string.IsNullOrEmpty(toAdd.TextFieldB))
                        query = query.Where(S => S.TextFieldB.Equals(null) || S.TextFieldB.Equals(""));
                    else
                    {
                        query = query.Where(S => S.TextFieldB.Equals(toAdd.TextFieldB));
                        sqlParameters += string.Format(", TextFieldB: '{0}'", toAdd.TextFieldB);
                    }
                }

                if (keyFiled == TableWorkOrder.cnTextFieldC)
                {
                    if (string.IsNullOrEmpty(toAdd.TextFieldC))
                        query = query.Where(S => S.TextFieldC.Equals(null) || S.TextFieldC.Equals(""));
                    else
                    {
                        query = query.Where(S => S.TextFieldC.Equals(toAdd.TextFieldC));
                        sqlParameters += string.Format(", TextFieldC: '{0}'", toAdd.TextFieldC);
                    }
                }

                if (keyFiled == TableWorkOrder.cnDateFieldA)
                {
                    if (toAdd.DateFieldA.HasValue)
                    {
                        query = query.Where(S => S.DateFieldA.Equals(toAdd.DateFieldA));
                        sqlParameters += string.Format(", DateFieldA: '{0}'", toAdd.DateFieldA);
                    }
                    else
                        query = query.Where(S => S.DateFieldA.Equals(null) || S.DateFieldA.Equals(""));
                }

                if (keyFiled == TableWorkOrder.cnDateFieldB)
                {
                    if (toAdd.DateFieldB.HasValue)
                    {
                        query = query.Where(S => S.DateFieldB.Equals(toAdd.DateFieldB));
                        sqlParameters += string.Format(", DateFieldB: '{0}'", toAdd.DateFieldB);
                    }
                    else
                        query = query.Where(S => S.DateFieldB.Equals(null));
                }

                if (keyFiled == TableWorkOrder.cnCustomerWorkOrderNumber)
                {
                    if (string.IsNullOrEmpty(toAdd.CustomerWorkOrderNumber))
                        query = query.Where(S => S.CustomerWorkOrderNumber.Equals(null) || S.CustomerWorkOrderNumber.Equals(""));
                    else
                    {
                        query = query.Where(S => S.CustomerWorkOrderNumber.Equals(toAdd.CustomerWorkOrderNumber));
                        sqlParameters += string.Format(", CustomerWorkOrderNumber: '{0}'", toAdd.CustomerWorkOrderNumber);
                    }
                }

                if (keyFiled == TableWorkOrder.cnSendDate)
                {
                    if (toAdd.SendDate.HasValue)
                    {
                        query = query.Where(S => S.SendDate.Equals(toAdd.SendDate));
                        sqlParameters += string.Format(", SendDate: '{0}'", toAdd.SendDate);
                    }
                    else
                        query = query.Where(S => S.SendDate.Equals(null) || S.SendDate.Equals(""));
                }

                if (keyFiled == TableWorkOrder.cnRequestedDelivaryDate)
                {
                    if (toAdd.RequestedDelivaryDate.HasValue)
                    {
                        query = query.Where(S => S.RequestedDelivaryDate.Equals(toAdd.RequestedDelivaryDate));
                        sqlParameters += string.Format(", RequestedDelivaryDate: '{0}'", toAdd.RequestedDelivaryDate);
                    }
                    else
                        query = query.Where(S => S.RequestedDelivaryDate.Equals(null) || S.RequestedDelivaryDate.Equals(""));
                }

                if (keyFiled == TableWorkOrder.cnContractorCatalogueCode)
                {
                    if (string.IsNullOrEmpty(toAdd.ContractorCatalogueCode))
                        query = query.Where(S => S.ContractorCatalogueCode.Equals(null) || S.ContractorCatalogueCode.Equals(""));
                    else
                    {
                        query = query.Where(S => S.ContractorCatalogueCode.Equals(toAdd.ContractorCatalogueCode));
                        sqlParameters += string.Format(", ContractorCatalogueCode: '{0}'", toAdd.ContractorCatalogueCode);
                    }
                }

                if (keyFiled == TableWorkOrder.cnContractorWorkOrderNumber)
                {
                    if (string.IsNullOrEmpty(toAdd.ContractorWorkOrderNumber))
                        query = query.Where(S => S.ContractorWorkOrderNumber.Equals(null) || S.ContractorWorkOrderNumber.Equals(""));
                    else
                    {
                        query = query.Where(S => S.ContractorWorkOrderNumber.Equals(toAdd.ContractorWorkOrderNumber));
                        sqlParameters += string.Format(", ContractorWorkOrderNumber: '{0}'", toAdd.ContractorWorkOrderNumber);
                    }
                }

                if (keyFiled == TableWorkOrder.cnRemarks)
                {
                    if (string.IsNullOrEmpty(toAdd.Remarks))
                        query = query.Where(S => S.Remarks.Equals(null) || S.Remarks.Equals(""));
                    else
                    {
                        query = query.Where(S => S.Remarks.Equals(toAdd.Remarks));
                        sqlParameters += string.Format(", Remarks: '{0}'", toAdd.Remarks);
                    }
                }

                if (keyFiled == TableWorkOrder.cnOrderNumber)
                {
                    if (string.IsNullOrEmpty(toAdd.OrderNumber))
                        query = query.Where(S => S.OrderNumber.Equals(null) || S.OrderNumber.Equals(""));
                    else
                    {
                        query = query.Where(S => S.OrderNumber.Equals(toAdd.OrderNumber));
                        sqlParameters += string.Format(", OrderNumber: '{0}'", toAdd.OrderNumber);
                    }
                }

                if (keyFiled == TableWorkOrder.cnActualDelivaryDate)
                {
                    if (toAdd.ActualDelivaryDate.HasValue)
                        query = query.Where(S => S.ActualDelivaryDate.Equals(toAdd.ActualDelivaryDate));
                    else
                    {
                        query = query.Where(S => S.ActualDelivaryDate.Equals(null) || S.ActualDelivaryDate.Equals(""));
                        sqlParameters += string.Format(", ActualDelivaryDate: '{0}'", toAdd.ActualDelivaryDate);
                    }
                }

                if (keyFiled == TableWorkOrder.cnPlannedDelivaryDate)
                {
                    if (toAdd.PlannedDelivaryDate.HasValue)
                        query = query.Where(S => S.PlannedDelivaryDate.Equals(toAdd.PlannedDelivaryDate));
                    else
                    {
                        query = query.Where(S => S.PlannedDelivaryDate.Equals(null) || S.PlannedDelivaryDate.Equals(""));
                        sqlParameters += string.Format(", PlannedDelivaryDate: '{0}'", toAdd.PlannedDelivaryDate);
                    }
                }

                if (keyFiled == TableWorkOrder.cnContractorLotNumber)
                {
                    if (string.IsNullOrEmpty(toAdd.ContractorLotNumber))
                        query = query.Where(S => S.ContractorLotNumber.Equals(null) || S.ContractorLotNumber.Equals(""));
                    else
                    {
                        query = query.Where(S => S.ContractorLotNumber.Equals(toAdd.ContractorLotNumber));
                        sqlParameters += string.Format(", ContractorLotNumber: '{0}'", toAdd.ContractorLotNumber);
                    }
                }

                if (keyFiled == TableWorkOrder.cnQuantity)
                {
                    if (toAdd.Quantity.HasValue)
                    {
                        query = query.Where(S => S.Quantity.Equals(toAdd.Quantity));
                        sqlParameters += string.Format(", Quantity: '{0}'", toAdd.Quantity);
                    }
                    else
                        query = query.Where(S => S.Quantity.Equals(null) || S.Quantity.Equals(""));
                }

                if (keyFiled == TableWorkOrder.cnCustomerCatalougeCode)
                {
                    if (string.IsNullOrEmpty(toAdd.CustomerCatalougeCode))
                        query = query.Where(S => S.CustomerCatalougeCode.Equals(null) || S.CustomerCatalougeCode.Equals(""));
                    else
                    {
                        query = query.Where(S => S.CustomerCatalougeCode.Equals(toAdd.CustomerCatalougeCode));
                        sqlParameters += string.Format(", CustomerCatalougeCode: '{0}'", toAdd.CustomerCatalougeCode);
                    }
                }

                if (keyFiled == TableWorkOrder.cnProductDescription)
                {
                    if (string.IsNullOrEmpty(toAdd.ProductDescription))
                        query = query.Where(S => S.ProductDescription.Equals(null) || S.ProductDescription.Equals(""));
                    else
                    {
                        query = query.Where(S => S.ProductDescription.Equals(toAdd.ProductDescription));
                        sqlParameters += string.Format(", ProductDescription: '{0}'", toAdd.ProductDescription);
                    }
                }
                if (keyFiled == TableWorkOrder.cnProject)
                {
                    if (string.IsNullOrEmpty(toAdd.Project))
                        query = query.Where(S => S.Project.Equals(null) || S.Project.Equals(""));
                    else
                    {
                        query = query.Where(S => S.Project.Equals(toAdd.Project));
                        sqlParameters += string.Format(", ProductDescription: '{0}'", toAdd.Project);
                    }
                }
                if (keyFiled == TableWorkOrder.cnoriginalActualDeliveryDate)
                {
                    if (toAdd.originalActualDeliveryDate.HasValue)
                    {
                        query = query.Where(S => S.originalActualDeliveryDate.Equals(toAdd.originalActualDeliveryDate));
                        sqlParameters += string.Format(", originalPlannedDeliveryDate: '{0}'", toAdd.originalActualDeliveryDate);
                    }
                    else
                        query = query.Where(S => S.originalActualDeliveryDate.Equals(null) || S.originalActualDeliveryDate.Equals(""));
                }
                if (keyFiled == TableWorkOrder.cndisqualifyDate)
                {
                    if (toAdd.originalActualDeliveryDate.HasValue)
                    {
                        query = query.Where(S => S.disqualifyDate.Equals(toAdd.disqualifyDate));
                        sqlParameters += string.Format(", disqualifyDate: '{0}'", toAdd.disqualifyDate);
                    }
                    else
                        query = query.Where(S => S.disqualifyDate.Equals(null) || S.disqualifyDate.Equals(""));
                }
                if (keyFiled == TableWorkOrder.cnoriginalWOForDisqualify)
                {
                    if (toAdd.originalWOForDisqualify.HasValue)
                    {
                        query = query.Where(S => S.originalWOForDisqualify.Equals(toAdd.originalWOForDisqualify));
                        sqlParameters += string.Format(", originalWOForDisqualify: '{0}'", toAdd.originalWOForDisqualify);
                    }
                    else
                        query = query.Where(S => S.originalWOForDisqualify.Equals(null) || S.originalWOForDisqualify.Equals(""));
                }
            }
            Log.WriteDebug(query.ToString(), "SQL Parameters: " + sqlParameters);
            List<TableWorkOrder> woToUpdate = query.ToList<TableWorkOrder>();


            //----------------------------------------------------

            // MAKE SURE UNIQUE ON ContractorWorkOrderNumber,SupplierWorkOrderNumber
            //List<TableWorkOrder> woToUpdate = dc.TableWorkOrders.Where(S =>
            //  S.CustomerId == toAdd.CustomerId &&
            //  S.ContractorId == toAdd.ContractorId &&
            // (S.ContractorWorkOrderNumber.Equals(toAdd.ContractorWorkOrderNumber) || toAdd.ContractorWorkOrderNumber == null) &&
            // (S.SupplierWorkOrderNumber.Equals(toAdd.SupplierWorkOrderNumber) || toAdd.SupplierWorkOrderNumber == null) &&
            // (S.SplitId == toAdd.SplitId || toAdd.SplitId == null) &&
            // (S.ContractorLotNumber == toAdd.ContractorLotNumber || toAdd.ContractorLotNumber == null) &&
            // S.originalWoForDist == null).ToList();

            if (woToUpdate.Count > 1)
            {
                if (Log.Instance.IsDebugEnabled)
                {
                    Log.WriteDebug("Number of work orders to update: " + woToUpdate.Count.ToString(),
                                   "First work worder: " + woToUpdate[0].ToStr());
                }
                throw new Exception(@"Unable to determine which work order to update. Too many work orders meet the criteria.");
            }

            if (woToUpdate.Count == 0)
            {
                DateTime MinDate = Utils.DateXWorkDaysAgo(DaysUpdateAlowed);
                //removed: DateTime.Now.AddDays(DaysUpdateAlowed);
                
                // Check that the dates are not too early. Done only for SCD/Recor connection
                if (IsSCDRecor(toAdd) && ToCheckDates)
                {
                    if (toAdd.ActualDelivaryDate < MinDate)
                        IncorrectDates += "ActualDeliveryDate cannot be inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    //if (toAdd.DateCreated < MinDate)
                    //    IncorrectDates += "DateCreated cannot be inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (toAdd.PlannedDelivaryDate < MinDate)
                        IncorrectDates += "PlannedDelivaryDate cannot be inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (toAdd.RequestedDelivaryDate < MinDate)
                        IncorrectDates += "RequestedDelivaryDate cannot be inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    //if (toAdd.SendDate < MinDate)
                    //    IncorrectDates += "SendDate cannot be inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (toAdd.DateFieldA < MinDate)
                        IncorrectDates += "DateFieldA cannot be inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (toAdd.DateFieldB < MinDate)
                        IncorrectDates += "DateFieldB cannot be inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (IncorrectDates != "")
                        throw new Exception(IncorrectDates);
                }
                // New for alert:
                toAdd.NewForAlert = true;
                toAdd.DateCreated = DateTime.Now;

                // lead time calculation for requestedDelivery date
                if (!toAdd.RequestedDelivaryDate.HasValue)
                {
                    toAdd.RequestedDelivaryDate = CalculateLeadTimeDays(toAdd.CustomerId, toAdd.ContractorId, toAdd.Quantity, toAdd.SendDate);
                }

                
                // Insert new record.
                toAdd.originalQuantity = toAdd.Quantity;
                dc.TableWorkOrders.InsertOnSubmit(toAdd);
                dc.SubmitChanges();
                return woStatus.inserted;
            }

            //Update current record.
            //
            decimal quantitySent;
            if (!decimal.TryParse(quantity_sent, out quantitySent))
                quantitySent = woToUpdate[0].Quantity.Value;
            if (quantitySent == 0) quantitySent = woToUpdate[0].Quantity.Value;

            // special case for Lithotech - no distributions, just updating the data.
            // if the quantity is lower - updating it.
            // TODO: change this to be for all the organizations that have disqualify reasons.
            if ((woToUpdate[0].CustomerId.HasValue) && 
                (woToUpdate[0].CustomerId.Value == 51) &&
                (quantitySent < woToUpdate[0].Quantity))
                quantitySent = woToUpdate[0].Quantity.Value;            

            // If the input Q (given in the field 'quantity_sent') is equal organizationRelation biggerr then 
            // the WO Q then update this WO. else, create distribution to this WO.
            Dal.TableWorkOrder wo = woToUpdate[0];

            if (quantitySent >= woToUpdate[0].Quantity)
            {
                if (!ToCheckDates || CheckDateForMin(toAdd.ActualDelivaryDate, woToUpdate[0].ActualDelivaryDate) || !IsSCDRecor(toAdd))
                    wo.ActualDelivaryDate = toAdd.ActualDelivaryDate != null ? toAdd.ActualDelivaryDate : woToUpdate[0].ActualDelivaryDate;
                else
                    IncorrectDates += "ActualDeliveryDate cannot be updated as it is older than " + DaysUpdateAlowed.ToString() + " days ago; ";

                wo.ContractorCatalogueCode = toAdd.ContractorCatalogueCode != null ? toAdd.ContractorCatalogueCode : woToUpdate[0].ContractorCatalogueCode;
                wo.ContractorId = toAdd.ContractorId != null ? toAdd.ContractorId : woToUpdate[0].ContractorId;
                wo.ContractorLotNumber = toAdd.ContractorLotNumber != null ? toAdd.ContractorLotNumber : woToUpdate[0].ContractorLotNumber;
                wo.ContractorWorkOrderNumber = toAdd.ContractorWorkOrderNumber != null ? toAdd.ContractorWorkOrderNumber : woToUpdate[0].ContractorWorkOrderNumber;
                wo.CustomerCatalougeCode = toAdd.CustomerCatalougeCode != null ? toAdd.CustomerCatalougeCode : woToUpdate[0].CustomerCatalougeCode;
                wo.CustomerId = toAdd.CustomerId != null ? toAdd.CustomerId : woToUpdate[0].CustomerId;
                wo.CustomerPriority = toAdd.CustomerPriority != null ? toAdd.CustomerPriority : woToUpdate[0].CustomerPriority;
                wo.CustomerWorkOrderNumber = toAdd.CustomerWorkOrderNumber != null ? toAdd.CustomerWorkOrderNumber : woToUpdate[0].CustomerWorkOrderNumber;
                //if (CheckDateForMin(toAdd.DateCreated, woToUpdate[0].DateCreated) || !IsSCDRecor(toAdd))
                    wo.DateCreated = toAdd.DateCreated != null ? toAdd.DateCreated : woToUpdate[0].DateCreated;
                //else
                    //    IncorrectDates += "DateCreated cannot be updated as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                wo.OrderNumber = toAdd.OrderNumber != null ? toAdd.OrderNumber : woToUpdate[0].OrderNumber;
                if (!ToCheckDates || CheckDateForMin(toAdd.PlannedDelivaryDate, woToUpdate[0].PlannedDelivaryDate) || !IsSCDRecor(toAdd))
                    wo.PlannedDelivaryDate = toAdd.PlannedDelivaryDate != null ? toAdd.PlannedDelivaryDate : woToUpdate[0].PlannedDelivaryDate;
                else
                    IncorrectDates += "PlannedDelivaryDate cannot be updated as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";

                wo.ProductDescription = toAdd.ProductDescription != null ? toAdd.ProductDescription : woToUpdate[0].ProductDescription;
                wo.Quantity = toAdd.Quantity != null ? toAdd.Quantity : woToUpdate[0].Quantity;
                wo.Remarks = toAdd.Remarks != null ? toAdd.Remarks : woToUpdate[0].Remarks;
                if (!ToCheckDates || CheckDateForMin(toAdd.RequestedDelivaryDate, woToUpdate[0].RequestedDelivaryDate) || !IsSCDRecor(toAdd)) 
                    wo.RequestedDelivaryDate = toAdd.RequestedDelivaryDate != null ? toAdd.RequestedDelivaryDate : woToUpdate[0].RequestedDelivaryDate;
                else
                    IncorrectDates += "RequestedDelivaryDate cannot be updated as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                // send date - allowing adding any date if the old date is null.
                if (!ToCheckDates || CheckDateForMin(toAdd.SendDate, woToUpdate[0].SendDate) || !IsSCDRecor(toAdd) || !woToUpdate[0].SendDate.HasValue) 
                    wo.SendDate = toAdd.SendDate != null ? toAdd.SendDate : woToUpdate[0].SendDate;
                else
                    IncorrectDates += "SendDate cannot be updated as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                wo.SplitId = toAdd.SplitId != null ? toAdd.SplitId : woToUpdate[0].SplitId;
                wo.TextFieldA = toAdd.TextFieldA != null ? toAdd.TextFieldA : woToUpdate[0].TextFieldA;
                wo.TextFieldB = toAdd.TextFieldB != null ? toAdd.TextFieldB : woToUpdate[0].TextFieldB;
                wo.TextFieldC = toAdd.TextFieldC != null ? toAdd.TextFieldC : woToUpdate[0].TextFieldC;
                if (!ToCheckDates || CheckDateForMin(toAdd.DateFieldA, woToUpdate[0].DateFieldA) || !IsSCDRecor(toAdd)) 
                    wo.DateFieldA = toAdd.DateFieldA != null ? toAdd.DateFieldA : woToUpdate[0].DateFieldA;
                else
                    IncorrectDates += "DateFieldA cannot be updated as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                if (!ToCheckDates || CheckDateForMin(toAdd.DateFieldB, woToUpdate[0].DateFieldB) || !IsSCDRecor(toAdd)) 
                    wo.DateFieldB = toAdd.DateFieldB != null ? toAdd.DateFieldB : woToUpdate[0].DateFieldB;
                else
                    IncorrectDates += "DateFieldB cannot be updated as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                wo.TotalSum = toAdd.TotalSum != null ? toAdd.TotalSum : woToUpdate[0].TotalSum;
                //wo.UpdateBy = toAdd.UpdateBy != null ? toAdd.UpdateBy : woToUpdate[0].UpdateBy;
                wo.UpdateBy = SessParam.UserId;
                //wo.UpdateDate = toAdd.UpdateDate != null ? toAdd.UpdateDate : woToUpdate[0].UpdateDate;
                //wo.UpdateDate = DateTime.Now.AddHours(-DateTime.Now.Hour).AddMinutes(-DateTime.Now.Minute).AddSeconds(-DateTime.Now.Second).AddMilliseconds(-DateTime.Now.Millisecond);
                wo.UpdateDate = DateTime.Now;
                wo.Project = toAdd.Project != null ? toAdd.Project : "";
                wo.debate = toAdd.debate;
                wo.originalActualDeliveryDate = toAdd.originalActualDeliveryDate;
                wo.disqualifyDate = toAdd.disqualifyDate;
                wo.originalWOForDisqualify = toAdd.originalWOForDisqualify;

                // If the given Q is bigger the the remaining Q of the WO, then increase the whole WO.
                if (quantitySent > woToUpdate[0].Quantity)
                {
                    wo.originalQuantity += quantitySent - wo.Quantity;
                }
                if (IncorrectDates != "")
                    throw new Exception(IncorrectDates);
            }
            // Else, the given Q is smaller then the remain Q fo the WO.
            // Create new dist and the given mData relate only to the dist.
            else
            {
                // checking whether ther are incorrect dates:
                if (ToCheckDates && IsSCDRecor(toAdd))
                {
                    if (!CheckDateForMin(toAdd.ActualDelivaryDate, woToUpdate[0].ActualDelivaryDate))
                        IncorrectDates += "ActualDeliveryDate cannot be updated or inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (!CheckDateForMin(toAdd.DateCreated, woToUpdate[0].DateCreated))
                        IncorrectDates += "DateCreated cannot be updated or inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (!CheckDateForMin(toAdd.PlannedDelivaryDate, woToUpdate[0].PlannedDelivaryDate))
                        IncorrectDates += "PlannedDelivaryDate cannot be updated or inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (!CheckDateForMin(toAdd.RequestedDelivaryDate, woToUpdate[0].RequestedDelivaryDate))
                        IncorrectDates += "RequestedDelivaryDate cannot be updated or inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (!CheckDateForMin(toAdd.DateFieldA, woToUpdate[0].DateFieldA))
                        IncorrectDates += "DateFieldA cannot be updated or inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                    if (CheckDateForMin(toAdd.DateFieldB, woToUpdate[0].DateFieldB))
                        IncorrectDates += "DateFieldB cannot be updated or inserted as it is older than " + DaysUpdateAlowed.ToString() + " days ago. ";
                }

                // if there are incorrect dates - through an exception
                if (IncorrectDates != "")
                    throw new Exception(IncorrectDates);

                Dal.TableWorkOrder t = Bl_Dist.DuplicateWo(toAdd);
                //t.Quantity = quantitySent; 
                // changed by Yoav's investigation, 2.9.2013
                t.Quantity = wo.Quantity - quantitySent;

                t.originalWoForDist = wo.WorkOrderId;

                // Create disrtribution only if the sent Q. is not null organizationRelation zero
                if (!string.IsNullOrEmpty(quantity_sent) && quantitySent != 0)
                    dc.TableWorkOrders.InsertOnSubmit(t);

                // Decrease the WO by the amount of the dist.
                wo.Quantity -= t.Quantity;

                // Update other fields of the original work order.
                if (cols.Contains(TableWorkOrder.cnContractorCatalogueCode))
                    wo.ContractorCatalogueCode = toAdd.ContractorCatalogueCode;
                if (cols.Contains(TableWorkOrder.cnContractorLotNumber))
                    wo.ContractorLotNumber = toAdd.ContractorLotNumber;
                if (cols.Contains(TableWorkOrder.cnContractorWorkOrderNumber))
                    wo.ContractorWorkOrderNumber = toAdd.ContractorWorkOrderNumber;
                if (cols.Contains(TableWorkOrder.cnCustomerCatalougeCode))
                    wo.CustomerCatalougeCode = toAdd.CustomerCatalougeCode;
                if (cols.Contains(TableWorkOrder.cnCustomerPriority))
                    wo.CustomerPriority = toAdd.CustomerPriority;
                if (cols.Contains(TableWorkOrder.cnCustomerWorkOrderNumber))
                    wo.CustomerWorkOrderNumber = toAdd.CustomerWorkOrderNumber;
                if (cols.Contains(TableWorkOrder.cnOrderNumber))
                    wo.OrderNumber = toAdd.OrderNumber;
                if (cols.Contains(TableWorkOrder.cnPlannedDelivaryDate))
                    wo.PlannedDelivaryDate = toAdd.PlannedDelivaryDate;
                if (cols.Contains(TableWorkOrder.cnProductDescription))
                    wo.ProductDescription = toAdd.ProductDescription;
                if (cols.Contains(TableWorkOrder.cnRemarks))
                    wo.Remarks = toAdd.Remarks;
                if (cols.Contains(TableWorkOrder.cnRequestedDelivaryDate))
                    wo.RequestedDelivaryDate = toAdd.RequestedDelivaryDate;
                if (cols.Contains(TableWorkOrder.cnSendDate))
                    wo.SendDate = toAdd.SendDate;
                if (cols.Contains(TableWorkOrder.cnTextFieldA))
                    wo.TextFieldA = toAdd.TextFieldA;
                if (cols.Contains(TableWorkOrder.cnTextFieldB))
                    wo.TextFieldB = toAdd.TextFieldB;
                if (cols.Contains(TableWorkOrder.cnTextFieldC))
                    wo.TextFieldC = toAdd.TextFieldC;
                if (cols.Contains(TableWorkOrder.cnDateFieldA))
                    wo.DateFieldA = toAdd.DateFieldA;
                if (cols.Contains(TableWorkOrder.cnDateFieldB))
                    wo.DateFieldB = toAdd.DateFieldB;
                if (cols.Contains(TableWorkOrder.cnProject))
                    wo.Project = toAdd.Project;
                if (cols.Contains(TableWorkOrder.cnoriginalWOForDisqualify))
                    wo.originalWOForDisqualify = toAdd.originalWOForDisqualify;

                wo.UpdateDate = DateTime.Now;
                wo.UpdateBy = SessParam.UserId;
            }           
            
            dc.SubmitChanges();
            // CR 12-02-2011 Matti: Distribution.
            Bll.clsWorkOrder.UpdateDists(wo.WorkOrderId);
            return woStatus.updated;
        }

        // returns true iff the new date can be added to the database. 
        // dates are blocked only if they are new, and are older than 3 days ago.
        private static bool CheckDateForMin(DateTime? NewDate, DateTime? OldDate)
        {
            if (!NewDate.HasValue)
                return true;
            else if (OldDate.HasValue && (NewDate.Value == OldDate.Value))
                return true;
            else if (NewDate.Value >= Utils.DateXWorkDaysAgo(3))
                return true;
            return false;
        }

        private static object GetVal(string t, Dictionary<string, object> dict, Type typeToCast)
        {
            try
            {
                if (dict[t].Equals("null"))
                    return null;
                return Convert.ChangeType(dict[t], typeToCast);
            }
            catch
            {
                return null;
            }
        }

        public static int InsertWoFromDictionary(Dictionary<string, object> dict)
        {
            Dal.TableWorkOrder toAdd = new MLY.Dal.TableWorkOrder();
            string colNames = "";
            foreach (string s in dict.Keys)
                colNames += s + ",";

            colNames = colNames.Substring(0, colNames.Length - 1);
            string[] cols = colNames.Split(',');
            object[] values = dict.Values.ToArray();

            toAdd.ContractorId = int.Parse(dict["ContractorId"].ToString());
            toAdd.CustomerId = int.Parse(dict["CustomerId"].ToString());

            toAdd.CustomerPriority = dict["CustomerPriority"].ToString() != "" ? int.Parse(dict["CustomerPriority"].ToString()) : 0;
            toAdd.SplitId = 0;
            toAdd.TextFieldA = (string)dict["TextFieldA"];
            toAdd.TextFieldB = (string)dict["TextFieldB"];
            toAdd.TextFieldC = (string)dict["TextFieldC"];
            toAdd.DateFieldA = Utils.ParseDate(dict["DateFieldA"].ToString());
            toAdd.DateFieldB = Utils.ParseDate(dict["DateFieldB"].ToString());
            toAdd.CustomerWorkOrderNumber = (string)dict["CustomerWorkOrderNumber"];
            toAdd.ContractorWorkOrderNumber = (string)dict["ContractorWorkOrderNumber"];
            toAdd.SendDate = Utils.ParseDate(dict["SendDate"].ToString());

            toAdd.RequestedDelivaryDate = Utils.ParseDate(dict["RequestedDelivaryDate"].ToString());
            toAdd.PlannedDelivaryDate = Utils.ParseDate(dict["PlannedDelivaryDate"].ToString());
            toAdd.ActualDelivaryDate = Utils.ParseDate(dict["ActualDelivaryDate"].ToString());

            toAdd.Quantity = decimal.Parse(dict["Quantity"].ToString());
            toAdd.originalQuantity = toAdd.Quantity;
            toAdd.CustomerCatalougeCode = (string)dict["CustomerCatalougeCode"];
            toAdd.ContractorCatalogueCode = (string)dict["ContractorCatalougeCode"];
            toAdd.ProductDescription = (string)dict["ProductDescription"];
            toAdd.Remarks = (string)dict["Remarks"];
            toAdd.OrderNumber = (string)dict["OrderNumber"];

            toAdd.Project = (string)dict["Project"];
            toAdd.debate = false;
            toAdd.originalActualDeliveryDate = Utils.ParseDate(dict["originalActualDeliveryDate"].ToString());
            toAdd.disqualifyDate = Utils.ParseDate(dict["disqualifyDate"].ToString());
            
            return add(toAdd);
        }

        public static List<Dal.TableWorkOrder> getDataFromWhere(string where, string sortDirection)
        {
            Dal.lqKbmDataContext dc = new MLY.Dal.lqKbmDataContext();

            dc.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues);
            if (where == "")
                return null;
            string[] args = where.ToLower().Split(new string[] { "and" }, StringSplitOptions.RemoveEmptyEntries);
            string newSql = "1=1";
            foreach (var item in args)
            {
                newSql += " AND ";
                string currItem = item.Trim();
                if (currItem.StartsWith("actualdelivarydate") || currItem.StartsWith("planneddelivarydate") || currItem.StartsWith("quantity"))
                    newSql += "(t." + currItem + " OR w." + currItem + ")";
                else
                    newSql += "w." + currItem;
            }
            string sql2 = String.Format(@"select * from TableWorkOrder w {0} ", newSql != string.Empty ? "where " + newSql : "");
            try
            {
                var x = dc.ExecuteQuery<Dal.TableWorkOrder>(sql2).ToList();
                return x.Where(S => (!S.ActualDelivaryDate.HasValue || S.ActualDelivaryDate.Value.Year < 2099) && (S.originalWoForDist == null)).ToList();
            }
            catch
            {
                return null;
            }
        }

        private static clsWorkOrder GetWoByNum(int num)
        {
            Dal.lqKbmDataContext dc = new MLY.Dal.lqKbmDataContext();
            Dal.TableWorkOrder i = (from p in dc.TableWorkOrders
                                    where p.WorkOrderId == num
                                    select p).SingleOrDefault();
            return (new clsWorkOrder(i));
        }

        //public static string addSplit(int woNum, decimal howMuchToSplit)
        //{
        //  Dal.lqKbmDataContext dc = new MLY.Dal.lqKbmDataContext();

        //  clsWorkOrder currOrder = GetWoByNum(woNum);
        //  if (howMuchToSplit > currOrder._DataRow.Quantity || currOrder._DataRow.Quantity == null)
        //    return "Quantity too large for split";
        //  if (String.IsNullOrEmpty(currOrder._DataRow.SupplierWorkOrderNumber) &&
        //      String.IsNullOrEmpty(currOrder._DataRow.ContractorWorkOrderNumber))
        //    return "Customer Wo Number and Contractor Wo Number Are null - not allowed!";
        //  clsWorkOrder newOrder = new clsWorkOrder(currOrder._DataRow);

        //  newOrder._DataRow.Quantity = howMuchToSplit;
        //  currOrder._DataRow.Quantity -= howMuchToSplit;

        //  List<Dal.TableWorkOrder> relevantOrders = (from p in dc.TableWorkOrders
        //                                             where p.WorkOrderId == currOrder._DataRow.WorkOrderId
        //                                             orderby p.SplitId descending
        //                                             select p).ToList();
        //  if (relevantOrders[0].SplitId == null)
        //  {
        //    newOrder._DataRow.SplitId = 1;
        //  }
        //  else
        //    newOrder._DataRow.SplitId = relevantOrders[0].SplitId + 1;

        //  dc.TableWorkOrders.InsertOnSubmit(newOrder._DataRow);
        //  dc.SubmitChanges();
        //  return "";
        //}

        public static decimal GetInventoryBalance(int woId, BalanceType balanceType)
        {
            using (Dal.lqKbmDataContext dc = new MLY.Dal.lqKbmDataContext())
            {
                Dal.TableWorkOrder wo = dc.TableWorkOrders.Where(S => S.WorkOrderId == woId).FirstOrDefault();

                if (wo.CustomerWorkOrderNumber == "12121212")
                {
                }

                DateTime? targetDate = null;

                decimal balance = 0;

                var deliv = from o in dc.TableWorkOrders
                            where o.originalWoForDist == woId &&
                                    o.WorkOrderId != woId
                            select o;

                foreach (TableWorkOrder dist in deliv.ToList())
                {
                    targetDate = (balanceType == BalanceType.ByRequestedDate ? wo.RequestedDelivaryDate : dist.PlannedDelivaryDate);
                    if (targetDate == null)
                        targetDate = new DateTime(2090, 1, 1);
                    else
                        targetDate = Utils.EndOfTheDay((DateTime)targetDate);

                    // If the target date has passed
                    if (DateTime.Today > targetDate)
                    {
                        if (dist.ActualDelivaryDate != null)
                        {
                            // Do nothing.
                            // If the target date has passed then the delivered distribution should not be counted
                            // as a positive value.
                            // It shouldn't be counted as negative Q since a dist. line mean actualDelivaryDate delivery.
                        }
                        else
                        {
                            if (dist.Quantity != null)
                                balance -= (decimal)dist.Quantity;
                        }
                    }
                    // Else, if it supplied before the target date then take it as positive value.
                    else
                    {
                        if (dist.ActualDelivaryDate != null && dist.ActualDelivaryDate != CONST_DateDeleted)
                            if (dist.Quantity != null)
                                balance += (decimal)dist.Quantity;
                    }
                }

                // Add the WO itself to the counter.
                targetDate = (balanceType == BalanceType.ByRequestedDate ? wo.RequestedDelivaryDate : wo.PlannedDelivaryDate);
                if (targetDate == null)
                    targetDate = new DateTime(2090, 1, 1);
                else
                    targetDate = Utils.EndOfTheDay((DateTime)targetDate);

                // If the target date has passed
                if (DateTime.Today > targetDate)
                {
                    // If it wasn't supplied ye take it as negative value.
                    // (otherwise, don't count it at all because Q. that was supplied on time is not counted after that date.)
                    if (wo.ActualDelivaryDate == null)
                    {
                        if (wo.Quantity != null)
                            balance -= (decimal)wo.Quantity;
                    }
                }
                // Else, if it supplied before the target date then take it as positive value.
                else
                {
                    if (wo.ActualDelivaryDate != null && wo.ActualDelivaryDate != CONST_DateDeleted)
                        if (wo.Quantity != null)
                            balance += (decimal)wo.Quantity;
                }
                return balance;
            }
        }


        public static Reports.AdvancedReports.dsAdvancedRpt.InventoryBalanceDetailsDataTable
                                                    GetInventoryBalanceDetailsForReport(int OrgId,
                                                                                int SelectedOrgId,
                                                                                DateTime DateStart,
                                                                                DateTime DateEnd)
        {
            Reports.AdvancedReports.dsAdvancedRpt.InventoryBalanceDetailsDataTable dt = new MLY.Reports.AdvancedReports.dsAdvancedRpt.InventoryBalanceDetailsDataTable();

            Bll.clsOrganization org = new clsOrganization(OrgId);
            DateEnd = Utils.EndOfTheDay(DateEnd);

            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                // Retrieve all WO in the period.
                var v = from o in db.TableWorkOrders
                        where (o.CustomerId == OrgId) &&
                                (o.originalWoForDist == null) &&
                                (o.ActualDelivaryDate == null || o.ActualDelivaryDate != new DateTime(2099, 1, 1)) &&
                                ((o.SendDate <= DateEnd) &&
                                    (o.PlannedDelivaryDate >= DateStart) ||
                                    (o.ActualDelivaryDate >= DateStart) ||
                                    (o.ActualDelivaryDate == null))
                        select o;
                if (SelectedOrgId != 0)
                    v = v.Where(o => o.ContractorId == SelectedOrgId);

                var vDist = from o in db.TableWorkOrders
                            join o2 in db.TableWorkOrders on o.WorkOrderId equals o2.originalWoForDist
                            where (o.CustomerId == OrgId) &&
                                    (o.ActualDelivaryDate == null || o.ActualDelivaryDate != new DateTime(2099, 1, 1)) &&
                                    ((o.SendDate <= DateEnd) &&
                                        (o.PlannedDelivaryDate >= DateStart) ||
                                        (o.ActualDelivaryDate >= DateStart) ||
                                        (o.ActualDelivaryDate == null))
                            select o2;
                if (SelectedOrgId != 0)
                    vDist = vDist.Where(o => o.ContractorId == SelectedOrgId);


                if (org.Data.IsClient == null || (bool)!org.Data.IsClient)
                {
                    v = from o in db.TableWorkOrders
                        where (o.ContractorId == OrgId) &&
                                (o.originalWoForDist == null) &&
                                (o.ActualDelivaryDate == null || o.ActualDelivaryDate != new DateTime(2099, 1, 1)) &&
                                ((o.SendDate <= DateEnd) &&
                                    (o.PlannedDelivaryDate >= DateStart) ||
                                    (o.ActualDelivaryDate >= DateStart) ||
                                    (o.ActualDelivaryDate == null))
                        select o;
                    if (SelectedOrgId != 0)
                        v = v.Where(o => o.CustomerId == SelectedOrgId);

                    vDist = from o in db.TableWorkOrders
                            join o2 in db.TableWorkOrders on o.WorkOrderId equals o2.originalWoForDist
                            where (o.ContractorId == OrgId) &&
                                    (o.ActualDelivaryDate == null || o.ActualDelivaryDate != new DateTime(2099, 1, 1)) &&
                                    ((o.SendDate <= DateEnd) &&
                                        (o.PlannedDelivaryDate >= DateStart) ||
                                        (o.ActualDelivaryDate >= DateStart) ||
                                        (o.ActualDelivaryDate == null))
                            select o2;
                    if (SelectedOrgId != 0)
                        vDist = vDist.Where(o => o.CustomerId == SelectedOrgId);

                }

                List<Dal.TableWorkOrder> workOrders = v.ToList();
                List<Dal.TableWorkOrder> distributions = vDist.ToList();
                List<DistributionEvent> allEvents = new List<DistributionEvent>();
                foreach (Dal.TableWorkOrder w in workOrders)
                {
                    // If the WO was supplied in the same date as plannedDelivaryDate then it is one event.
                    if ((w.PlannedDelivaryDate != null) &&
                        (w.ActualDelivaryDate != null) &&
                        (w.PlannedDelivaryDate.Value.Date == w.ActualDelivaryDate.Value.Date))
                    {
                        DistributionEvent de = new DistributionEvent();
                        de.EventDate = w.PlannedDelivaryDate.Value.Date;
                        de.Quantity = (w.Quantity == null ? 0 : (decimal)w.Quantity);
                        decimal? wsum = distributions.Where(o => o.originalWoForDist == w.WorkOrderId).Sum(o => o.Quantity);
                        if (wsum == null)
                            wsum = 0;
                        de.Value = -(decimal)wsum;
                        de.WorkOrder = w;
                        allEvents.Add(de);
                    }
                    // Else, there are two events in two dates. one for the delivery and second for the target date.
                    else
                    {
                        if (w.PlannedDelivaryDate != null)
                        {
                            DistributionEvent de = new DistributionEvent();
                            de.EventDate = w.PlannedDelivaryDate.Value.Date;
                            de.Quantity = 0;
                            //decimal? wsum = distributions.Where(org => org.originalWoForDist == w.WorkOrderId).Sum(org => org.Quantity);
                            //if (wsum == null)
                            //    wsum = 0;
                            decimal? wsum = 0;
                            wsum += (w.originalQuantity == null ? 0 : (decimal)w.originalQuantity);
                            de.Value = -(wsum == null ? 0 : (decimal)wsum);
                            de.WorkOrder = w;
                            allEvents.Add(de);
                        }
                        if (w.ActualDelivaryDate != null)
                        {
                            DistributionEvent de = new DistributionEvent();
                            de.EventDate = w.ActualDelivaryDate.Value.Date;
                            decimal? wsum = distributions.Where(o => o.originalWoForDist == w.WorkOrderId).Sum(o => o.Quantity);
                            if (wsum == null)
                                wsum = 0;
                            wsum = (w.originalQuantity == null ? 0 : (decimal)w.originalQuantity) - wsum;
                            de.Quantity = (decimal)wsum;
                            de.Value = (decimal)wsum;
                            de.WorkOrder = w;
                            allEvents.Add(de);
                        }
                    }
                }

                foreach (Dal.TableWorkOrder dis in distributions)
                {
                    // If the WO was supplied in the same date as plannedDelivaryDate then it is one event.
                    if ((dis.PlannedDelivaryDate != null) &&
                        (dis.ActualDelivaryDate != null) &&
                        (dis.PlannedDelivaryDate.Value.Date == dis.ActualDelivaryDate.Value.Date))
                    {
                        DistributionEvent de = new DistributionEvent();
                        de.EventDate = dis.ActualDelivaryDate.Value.Date;
                        de.Quantity = (dis.Quantity == null ? 0 : (decimal)dis.Quantity);
                        de.Value = 0;   //if it supplied as plannedDelivaryDate then no change in the balanc.
                        de.WorkOrder = dis;
                        allEvents.Add(de);
                    }
                    // Else, there are two events in two dates. one for the delivery and second for the target date.
                    else
                    {
                        if (dis.PlannedDelivaryDate != null)
                        {
                            DistributionEvent de = new DistributionEvent();
                            de.EventDate = dis.PlannedDelivaryDate.Value.Date;
                            de.Quantity = 0;
                            de.Value = -(dis.Quantity == null ? 0 : (decimal)dis.Quantity); ;
                            de.WorkOrder = dis;
                            allEvents.Add(de);
                        }
                        if (dis.ActualDelivaryDate != null)
                        {
                            DistributionEvent de = new DistributionEvent();
                            de.EventDate = dis.ActualDelivaryDate.Value.Date;
                            de.Quantity = (dis.Quantity == null ? 0 : (decimal)dis.Quantity);
                            de.Value = (dis.Quantity == null ? 0 : (decimal)dis.Quantity);
                            de.WorkOrder = dis;
                            allEvents.Add(de);
                        }
                    }
                }

                Dictionary<int, string> names = new Dictionary<int, string>();
                foreach (DistributionEvent dist in allEvents.OrderBy(o => o.EventDate))
                {
                    Reports.AdvancedReports.dsAdvancedRpt.InventoryBalanceDetailsRow dr = dt.NewInventoryBalanceDetailsRow();
                    dr.ActualDeliveryDate = dist.EventDate;
                    dr.ContractorCatalogCode = dist.WorkOrder.ContractorCatalogueCode;
                    dr.CustomerCatalogCode = dist.WorkOrder.CustomerCatalougeCode;
                    dr.Description = dist.WorkOrder.ProductDescription;
                    int? relId = 0;
                    string orgName = "";
                    if (org.Data.IsClient != null && (bool)org.Data.IsClient)
                        relId = dist.WorkOrder.ContractorId;
                    else
                        relId = dist.WorkOrder.CustomerId;
                    if (relId == null)
                        relId = 0;
                    if (names.ContainsKey((int)relId))
                    {
                        orgName = names[(int)relId];
                    }
                    else
                    {
                        Bll.clsOrganization relOrg = new clsOrganization((int)relId);
                        if (relOrg.IsExist)
                        {
                            names[(int)relId] = relOrg.Data.OrganizationName;
                            orgName = relOrg.Data.OrganizationName;
                        }
                    }
                    dr.OrganizationName = orgName;
                    dr.PlannedDeliveryDate = (dist.WorkOrder.PlannedDelivaryDate == null ? new DateTime(1999, 1, 1) : (DateTime)dist.WorkOrder.PlannedDelivaryDate);
                    dr.Quantity = dist.Quantity;
                    dr.SendDate = (dist.WorkOrder.SendDate == null ? new DateTime(1999, 1, 1) : (DateTime)dist.WorkOrder.SendDate);
                    dr.Value = dist.Value;
                    if (org.Data.IsClient != null && (bool)org.Data.IsClient)
                        dr.WorkOrderNumber = dist.WorkOrder.CustomerWorkOrderNumber;
                    else
                        dr.WorkOrderNumber = dist.WorkOrder.ContractorWorkOrderNumber;

                    dt.Rows.Add(dr);
                }

            }

            return dt;

        }

        public static Reports.AdvancedReports.dsAdvancedRpt.InventoryBalanceDetailsDataTable
                                                    GetInventoryBalanceForReport(int OrgId,
                                                                                int SelectedOrgId,
                                                                                DateTime DateStart,
                                                                                DateTime DateEnd)
        {
            Reports.AdvancedReports.dsAdvancedRpt.InventoryBalanceDetailsDataTable dt = new MLY.Reports.AdvancedReports.dsAdvancedRpt.InventoryBalanceDetailsDataTable();

            Bll.clsOrganization org = new clsOrganization(OrgId);
            DateEnd = Utils.EndOfTheDay(DateEnd);

            using (Dal.lqKbmDataContext db = new MLY.Dal.lqKbmDataContext())
            {
                // Retrieve all WO in the period.
                var v = from o in db.TableWorkOrders
                        where (o.CustomerId == OrgId) &&
                                (o.originalWoForDist == null) &&
                                (o.ActualDelivaryDate == null || o.ActualDelivaryDate != new DateTime(2099, 1, 1)) &&
                                (
                                    (o.SendDate <= DateEnd) &&
                                    (
                                        o.RequestedDelivaryDate >= DateStart ||
                                        o.ActualDelivaryDate == null ||
                                        o.ActualDelivaryDate >= DateStart
                                    )
                                )
                        select o;
                if (SelectedOrgId != 0)
                    v = v.Where(o => o.ContractorId == SelectedOrgId);

                var vDist = from o in db.TableWorkOrders
                            join o2 in db.TableWorkOrders on o.WorkOrderId equals o2.originalWoForDist
                            where (o.CustomerId == OrgId) &&
                                    (o.ActualDelivaryDate == null || o.ActualDelivaryDate != new DateTime(2099, 1, 1)) &&
                                    (
                                        (o.SendDate <= DateEnd) &&
                                        (
                                            o.RequestedDelivaryDate >= DateStart ||
                                            o.ActualDelivaryDate == null ||
                                            o.ActualDelivaryDate >= DateStart
                                        )
                                    )
                            select o2;
                if (SelectedOrgId != 0)
                    vDist = vDist.Where(o => o.ContractorId == SelectedOrgId);

                if (org.Data.IsClient == null || (bool)!org.Data.IsClient)
                {
                    v = from o in db.TableWorkOrders
                        where (o.ContractorId == OrgId) &&
                                (o.originalWoForDist == null) &&
                                (o.ActualDelivaryDate == null || o.ActualDelivaryDate != new DateTime(2099, 1, 1)) &&
                                (
                                    (o.SendDate <= DateEnd) &&
                                    (
                                        o.RequestedDelivaryDate >= DateStart ||
                                        o.ActualDelivaryDate == null ||
                                        o.ActualDelivaryDate >= DateStart
                                    )
                                )
                        select o;
                    if (SelectedOrgId != 0)
                        v = v.Where(o => o.CustomerId == SelectedOrgId);

                    vDist = from o in db.TableWorkOrders
                            join o2 in db.TableWorkOrders on o.WorkOrderId equals o2.originalWoForDist
                            where (o.ContractorId == OrgId) &&
                                    (o.originalWoForDist == null) &&
                                    (o.ActualDelivaryDate == null || o.ActualDelivaryDate != new DateTime(2099, 1, 1)) &&
                                    (
                                        (o.SendDate <= DateEnd) &&
                                        (
                                            o.RequestedDelivaryDate >= DateStart ||
                                            o.ActualDelivaryDate == null ||
                                            o.ActualDelivaryDate >= DateStart
                                        )
                                    )
                            select o2;
                    if (SelectedOrgId != 0)
                        vDist = vDist.Where(o => o.CustomerId == SelectedOrgId);
                }

                List<Dal.TableWorkOrder> workOrders = v.ToList();
                List<Dal.TableWorkOrder> distributions = vDist.ToList();

                Dictionary<int, string> names = new Dictionary<int, string>();
                foreach (Dal.TableWorkOrder w in workOrders)
                {
                    Reports.AdvancedReports.dsAdvancedRpt.InventoryBalanceDetailsRow dr = dt.NewInventoryBalanceDetailsRow();
                    if (w.ActualDelivaryDate == null)
                        dr.SetActualDeliveryDateNull();
                    else
                        dr.ActualDeliveryDate = (DateTime)w.ActualDelivaryDate;
                    dr.ContractorCatalogCode = w.ContractorCatalogueCode;
                    dr.CustomerCatalogCode = w.CustomerCatalougeCode;
                    dr.Description = w.ProductDescription;
                    int? relId = 0;
                    string orgName = "";
                    if (org.Data.IsClient != null && (bool)org.Data.IsClient)
                        relId = w.ContractorId;
                    else
                        relId = w.CustomerId;
                    if (relId == null)
                        relId = 0;
                    if (names.ContainsKey((int)relId))
                    {
                        orgName = names[(int)relId];
                    }
                    else
                    {
                        Bll.clsOrganization relOrg = new clsOrganization((int)relId);
                        if (relOrg.IsExist)
                        {
                            names[(int)relId] = relOrg.Data.OrganizationName;
                            orgName = relOrg.Data.OrganizationName;
                        }
                    }
                    dr.OrganizationName = orgName;
                    if (w.PlannedDelivaryDate == null)
                        dr.SetPlannedDeliveryDateNull();
                    else
                        dr.PlannedDeliveryDate = (DateTime)w.PlannedDelivaryDate;
                    if (w.RequestedDelivaryDate == null)
                        dr.SetRequestedDeliveryDateNull();
                    else
                        dr.RequestedDeliveryDate = (DateTime)w.RequestedDelivaryDate;
                    // 'cause Matan update - distribution action don't decrease the WO quantity.
                    //decimal? wsum = distributions.Where(org => org.originalWoForDist == w.WorkOrderId).Sum(org => org.Quantity);
                    //if (wsum == null)
                    //    wsum = 0;
                    decimal? wsum = 0;
                    wsum += (w.originalQuantity == null ? 0 : (decimal)w.originalQuantity);
                    dr.Quantity = (decimal)wsum;
                    if (w.SendDate == null)
                        dr.SetSendDateNull();
                    else
                        dr.SendDate = (DateTime)w.SendDate;
                    dr.Value = GetInventoryBalance(w.WorkOrderId, BalanceType.ByRequestedDate);
                    if (org.Data.IsClient != null && (bool)org.Data.IsClient)
                        dr.WorkOrderNumber = w.CustomerWorkOrderNumber;
                    else
                        dr.WorkOrderNumber = w.ContractorWorkOrderNumber;

                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

        public Dal.TableWorkOrder Data
        {
            get { return _DataRow; }
        }

        lqKbmDataContext dc = new lqKbmDataContext();
        public bool IsExist = false;
        TableWorkOrder _DataRow = null;

        public class DistributionEvent
        {
            public DateTime EventDate { get; set; }
            public decimal Quantity { get; set; }
            public decimal Value { get; set; }
            public decimal Balance { get; set; }
            public Dal.TableWorkOrder WorkOrder { get; set; }
        }


        private static DateTime? CalculateLeadTimeDays(int? customerId, int? contractorId, decimal? Quantity, DateTime? SendDate)
        {
            DateTime? reqDate = null;
            if (customerId.HasValue && contractorId.HasValue && Quantity.HasValue)
            {
                using (lqKbmDataContext dc = new lqKbmDataContext())
                {
                    decimal? leadTime = (from orgr in dc.TableOrganizationRelations
                                         where orgr.ContractorId == contractorId && orgr.CustomerId == customerId
                                         select orgr.DefaultTimeDurationDays).FirstOrDefault();

                    if (leadTime.HasValue)
                    {
                        double DaysToAdd = (double)ConvertRegDaysToWorkDays((int)((leadTime.Value * Quantity.Value) / 9));
                        if(SendDate.HasValue)
                            reqDate = SendDate.Value.AddDays(DaysToAdd);
                        else
                            reqDate = DateTime.Now.AddDays(DaysToAdd);
                    }
                }
            }

            return reqDate;
        }

        public static int ConvertRegDaysToWorkDays(int NormDays)
        {
            int WorkDays = 0;
            int CurrentDay = (int)DateTime.Now.DayOfWeek;

            while (NormDays > 7)
            {
                WorkDays += 5;
                NormDays -= 7;
            }
            WorkDays += NormDays;
            if (NormDays + CurrentDay > 5)
                WorkDays += 2;

            if (CurrentDay == 6)
                WorkDays += 2;
            else if (CurrentDay == 7)
                WorkDays += 1;

            return WorkDays;
        }
    }
}